LEASE CALCULATOR Client Name Phone Fax E-mail Lease Terms Input Data Purchase Price Total Number of Payments* Payment Periods per annum Advance (1) / Arrears (0) Benchmark Rate Other lease costs per period Lease Payment per period Financier's Lease Margin Capital User Charge
*Maximum lease period is 60 (monthly for 5 years)
Inputs Agency Division/Branch/Section: Description of Equipment:
Summary Information Implied Residual (Non-standard payments) Lease Rate per annum Lease payment per period Lease Payments per annum $0.00 Total Lease Payments Total purchase cost (incl. CUC) Net Nominal benefit to leasing* #DIV/0! 0.00% $0.00 $0.00 $0.00 #VALUE! #VALUE! (negative implies cost) #DIV/0! #DIV/0! per period
8.00%
*Ignores time value of money: purchasing requires upfront payment whilst leasing amortises value of asset over its life
Estimated Residual* Input Data % of Purch price Either: Maximum possible residual Minimum Possible residual Or: Expected Value of Residual Range of Residual (Downside) #DIV/0! Certainty Equivalent Amount Either: Recommended maximum payment to remove downside risk Or: Maximum payment to remove downside risk (input) $ at end of lease term
*see attached worksheet Summary Information
Minimum amount Agency would accept with certainty Disposal Risk High if downside is >= 100% Medium if downside is >= 50% and < 100% Low if downside is < 50%
*see graph
Indicative Range of Outcomes* Amount Highest Expected Value Lowest (downside)
Probability 10% 20% 40% 20% 10% 100%
3a0c3724-d466-4e95-8297-3db7cce77653.xls
1
8/13/2008
Financial Performance Operating versus Finance Lease: Lease as quoted: Minimum Required for Operating Lease: Maximum permissible for Operating Lease: Result: Residual Comparison Estimated (average) residual to agency Lease Residual (Implied)
Sumary Residual #DIV/0! #NUM! #DIV/0! #DIV/0! % Lease Rate 0.00% #NUM! 4.00% Margin 0.00% Present value test #DIV/0! (Present value of lease payments must fall below 90% to qualify as an Operating Lease) #NUM! Effective Life Test (Life) 4.00% #DIV/0! (Lease cannot cover more than 75% of Effective Life of asset) #DIV/0!
$ Maximum Estimated Residual Minimum Estimated Residual Residual Range #DIV/0! Agency Discount Rate Government Discount Rate Financier Lease Rate #DIV/0! #DIV/0! 0.00%
#DIV/0!
#DIV/0!
Result: Comparison of Interest Rates Capital User Charge Asset risk premium Implied Agency Discount Rate Benchmark Rate Financier's Lease premium Financier's Lease Rate Result: Residual Risk Assessment Effective Unrisked Residual Certainty Equivalent Amount Benefit Result: Financial Assessment Unrisked Cost of lease payments (PV) Less payment to remove risk (PV) Total Cost of Lease (PV) Net Cost of Purchase (PV) Benefit of leasing over purchase (PV)
#DIV/0!
8.00% #DIV/0! #DIV/0! 0.00% 0.00% 0.00% #DIV/0!
(Lease rate is competitive if lower than Agency/Government Rate) (Implied Risk premium applied to residual only) #DIV/0! #DIV/0!
#DIV/0! #VALUE!
#DIV/0! #DIV/0!
#DIV/0!
#DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #NUM! #DIV/0! #DIV/0!
The PV benefit (cost) of leasing over purchasing to the Agency is: made up of from difference between Agency's risk-adjusted discount rate and Financier's Lease Rate; and (PV) from differences in residual value estimates #NUM! #NUM! #NUM! #NUM! #DIV/0! #DIV/0!
#DIV/0!
Internal Rate of Return (effective cost of leasing pa) Agency Discount Rate (Max.acceptable cost pa) Government Discount Rate Result: #NUM!
3a0c3724-d466-4e95-8297-3db7cce77653.xls
2
8/13/2008
Theoretical Residual Values on the Sale of Equipment - Worksheet
Sales Purchase Value ie Price Residual Probability Worksheet 1 $2,000.00 2% 0.025 4% 0.050 6% 0.100 8% 0.200 10% 0.250 12% 0.200 14% 0.100 16% 0.050 18% 0.025 1.000
Comments This worksheet allows the user to input both the estimated residual and the probability of each outcome ocurring This can be used to explore more fully the range of outcomes and probable downside The results give both the "expected value" or average and the downside range which encompassing 90% of outcomes Hence 90% of outcomes lie above the downside range Downside range is given as 1.28 times the standard deviation These values can then be used in the lease calculator Expected Residual (%) Maximum (%) 14.46% Downside/Range (%) Minimum (%) 5.62% Expected Residual ($) Maximum ($) $289.23 Downside/Range ($) Minimum ($) $112.37 This worksheet allows the user to estimate a range of outcomes but use a predifined range of probabilities The results give both the "expected value" (or average) and the range, to be inputed into the Lease Calculator 10.00% 7.01% $200.00 $140.22 Expected Residual (%) Downside/Range (%) Expected Residual ($) Downside/Range ($) Maximum (%) Minimum (%) Maximum ($) Minimum ($) 17.01% 2.99% $340.22 $59.78
10.04% 4.42% $200.80 $88.43
Worksheet 2 $2,000.00
25% 20% 15% 10% 5% 0% -5%
0.000 0.100 0.200 0.400 0.200 0.100 0.000 1.000
Example 1 $2,000.00
30% 20% 15% 10% 5% 3% 0% -5% -10%
0.000 0.000 0.000 0.600 0.400 0.000 0.000 0.000 0.000 1.000
8.00% 3.14% $160.00 $62.71
A simple example There are two possible outcomes, a residual of 10% with 60% chance and a residual of 5% with 40% chance Therefore the expected value is 8% These are treated as two possible outcomes in a sample Imposing a normal distribution, 90% of outcomes occur above $97.29 We are prepared to pay half the downside of $62.71 to eliminate risk Thus payment to remove risk is $31.35 Expected Residual (%) Maximum (%) 11.14% Downside/Range (%) Minimum (%) 4.86% Expected Residual ($) Maximum ($) $222.71 Downside/Range ($) Minimum ($) $97.29
Example 2 $2,000.00
30% 20% 15% 10% 5% 3% 0% -5% -10%
0.000 0.000 0.100 0.500 0.300 0.030 0.030 0.020 0.020 1.000
7.78% 6.04% $155.50 $120.72
Another simple example Using 10% Sales value as most likely estimate, then there is 10% upside and a 40% down side probability Expected residual value is 7.78% Therefore the downside risk is $120.72 Imposing a normal distribution, 90% of outcomes occur above $34.78 We are prepared to pay half the downside of $120.72 to eliminate risk Thus payment to remove risk is $60.36 Expected Residual (%) Maximum (%) 13.81% Downside/Range (%) Minimum (%) 1.74% Expected Residual ($) Maximum ($) $276.22 Downside/Range ($) Minimum ($) $34.78
Example 3 $2,000.00
20% 15% 10% 5% 3% 0% -5% -10%
0.025 0.050 0.100 0.500 0.150 0.100 0.050 0.025 1.000
Using 5% Sales value as most likely estimate, then expected residual value is 4.13% The downside risk is 4.13% - (-10%) Imposing a normal distribution, 90% of outcomes occur above -$51.25 We are prepared to pay half the downside of $133.75 to eliminate risk Thus payment to remove risk is $66.88 4.13% Expected Residual (%) Maximum (%) 10.81% 6.69% Downside/Range (%) Minimum (%) -2.56% $82.50 Expected Residual ($) Maximum ($) $216.25
Theoretical Residual Values on the Sale of Equipment - Worksheet
Purchase Price Sales Value ie Residual
Probability
Comments $133.75 Downside/Range ($)
Minimum ($)
-$51.25
Theoretical Residual Values on the Sale of Equipment - Worksheet
Sales Purchase Value ie Price Residual Probability Example 4 $2,000.00 20% 0.020 15% 0.050 10% 0.060 5% 0.710 3% 0.060 0% 0.050 -5% 0.030 -10% 0.020 1.000
Comments
4.70% 5.76% $94.00 $115.27
Using 5% Sales value as most likely estimate, then there is 13% upside and a 16% down side probability Expected residual value is 4.70% Therefore the downside risk is $115.27 Imposing a normal distribution, 90% of outcomes occur above -$21.27 We are prepared to pay half the downside of $115.27 to eliminate risk Thus payment to remove risk is $57.64 Expected Residual (%) Maximum (%) 10.46% Downside/Range (%) Minimum (%) -1.06% Expected Residual ($) Maximum ($) $209.27 Downside/Range ($) Minimum ($) -$21.27
Example 5 $2,000.00
20% 15% 10% 5% 3% 0% -5% -10%
0.050 0.150 0.230 0.300 0.170 0.050 0.030 0.020 1.000
6.13% 7.92% $122.50 $158.42
Using 5% Sales value as most likely estimate, then there is 43% upside and a 27% down side probability Expected residual value is 6.1% Therefore the downside risk is $158.42 Imposing a normal distribution, 90% of outcomes occur above -$35.92 We are prepared to pay half the downside of $158.42 to eliminate risk Thus payment to remove risk is $79.21 Expected Residual (%) Maximum (%) 14.05% Downside/Range (%) Minimum (%) -1.80% Expected Residual ($) Maximum ($) $280.92 Downside/Range ($) Minimum ($) -$35.92
Example 6 $2,000.00
30% 20% 15% 10% 5% 3% 0% -5% -10%
0.030 0.120 0.180 0.250 0.180 0.120 0.070 0.030 0.020 1.000
6.05% 10.86% $121.00 $217.17
Using 10% Sales value as most likely estimate, then there is 33% upside and a 42% down side probability Expected residual value is 6.0% Expected value of down side is 16% Therefore the downside risk is $217.17 Imposing a normal distribution, 90% of outcomes occur above -$96.17 We are prepared to pay half the downside of $217.17 to eliminate risk Thus payment to remove risk is $108.59 Expected Residual (%) Maximum (%) 16.91% Downside/Range (%) Minimum (%) -4.81% Expected Residual ($) Maximum ($) $338.17 Downside/Range ($) Minimum ($) -$96.17
Mean
Low Risk Medium Risk High Risk Std Dev(m) 30 50 120 10 3 0 #VALUE! #VALUE! #VALUE! 9.5 2.85 #VALUE! #VALUE! #VALUE! #VALUE! Probability (%) 9 2.7 #VALUE! #VALUE! #VALUE! #VALUE! 8.5 2.55 #VALUE! #VALUE! #VALUE! #VALUE! 100.0% 8 2.4 #VALUE! #VALUE! #VALUE! #VALUE! 7.5 2.25 #VALUE! #VALUE! #VALUE! #VALUE! The higher the risk of attaining the 90.0% 7 2.1 #VALUE! #VALUE! #VALUE! #VALUE! expected residual, the greater the 6.5 1.95 #VALUE! #VALUE! #VALUE! #VALUE! 80.0% 6 dispersion of possible outcomes about#VALUE! #VALUE! the 1.8 #VALUE! #VALUE! centre#VALUE! flatter the curve 5.5 1.65 and the #VALUE! #VALUE! #VALUE! 70.0% 5 1.5 #VALUE! #VALUE! #VALUE! #VALUE! 4.5 1.35 #VALUE! demonstrate #VALUE! of These curves that 90% #VALUE! #VALUE! 60.0% 4 1.2 #VALUE! occur above #VALUE! #VALUE! #VALUE! outcomes will the minimum 3.5 1.05 #VALUE! #VALUE! #VALUE! #VALUE! residual specified and only 10% of 50.0% 3 0.9 #VALUE! #VALUE! #VALUE! #VALUE! 2.5 0.75 #VALUE! #VALUE! #VALUE! #VALUE! 0.6 #VALUE! #VALUE! #VALUE! #VALUE! 40.0% 2 1.5 0.45 #VALUE! #VALUE! #VALUE! #VALUE! 0.3 #VALUE! #VALUE! #VALUE! #VALUE! 30.0% 1 0.5 0.15 #VALUE! #VALUE! #VALUE! #VALUE! 0 0 #VALUE! #VALUE! #VALUE! 20.0% 0.5 0.15 #VALUE! #VALUE! #VALUE! #VALUE! 1 0.3 #VALUE! #VALUE! #VALUE! #VALUE! 10.0% 1.5 0.45 #VALUE! #VALUE! #VALUE! #VALUE! 2 0.6 #VALUE! #VALUE! #VALUE! #VALUE! 0.0% 2.5 0.75 #VALUE! #VALUE! #VALUE! #VALUE! 3 0.9 #VALUE! #VALUE! #VALUE! #VALUE! 3.5 1.05 #VALUE! #VALUE! #VALUE! #VALUE! 4 1.2 #VALUE! #VALUE! #VALUE! #VALUE! 4.5 1.35 #VALUE! #VALUE! #VALUE! #VALUE! 5 1.5 #VALUE! #VALUE! #VALUE! #VALUE! 5.5 1.65 #VALUE! #VALUE! #VALUE! #VALUE! 6 1.8 #VALUE! #VALUE! #VALUE! #VALUE! 6.5 1.95 #VALUE! #VALUE! #VALUE! #VALUE! 7 2.1 #VALUE! #VALUE! #VALUE! #VALUE! 7.5 2.25 #VALUE! #VALUE! #VALUE! #VALUE! 8 2.4 #VALUE! #VALUE! #VALUE! #VALUE! 8.5 2.55 #VALUE! #VALUE! #VALUE! #VALUE! 9 2.7 #VALUE! #VALUE! #VALUE! #VALUE! 9.5 2.85 #VALUE! #VALUE! #VALUE! #VALUE! 10 3 #VALUE! #VALUE! #VALUE! #VALUE!
Distribution of Possible Residual Val
Residual Values
Low Risk
Medium Risk High Risk
Residual Value
SENSITIVITY OF SAVINGS FROM LEASING TO GOVT DISCOUNT RATE
1
Savings from Leasing (PV$ per $000 )
1
1 1 1 1 0 0
0
0 5%
-
Higher Government Discount Rates are associated with a greater level of risk in achieving the expected residual value. As the risk / discount rate rises, an agency will pay more to remove risk and leasing becomes increasingly attractive.
-
-
-
-
-
-
6%
7%
8%
9%
10%
11%
12%
13%
14%
15%
Government Discount Rate
3a0c3724-d466-4e95-8297-3db7cce77653.xls 8/13/2008